package com.fduss.artStore.dao.impl;

import com.fduss.artStore.dao.ArtworkDao;
import com.fduss.artStore.entity.Artwork;
import com.fduss.artStore.util.JdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by alex on 27/06/2017.
 */
public class ArtworkDaoImpl implements ArtworkDao {
    private JdbcUtil util;

    public ArtworkDaoImpl() {
        this.util = new JdbcUtil();
    }


    @Override
    public Artwork add(Artwork artwork) {
        Connection conn = util.getConnection();
        String sql = "INSERT INTO `art`.`artwork` " +
                "(`ArtWorkID`, `ArtistID`, `ImageFileName`, `Title`, `Description`, `Excerpt`," +
                " `ArtWorkType`, `YearOfWork`, `Width`, `Height`, `Medium`, `OriginalHome`, `GalleryID`," +
                " `Cost`, `MSRP`, `ArtWorkLink`, `GoogleLink`) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement pst = null;
        boolean flag = false;

        try{
            pst = conn.prepareStatement(sql);
            pst.setInt(1, artwork.getArtWorkId());
            pst.setInt(2, artwork.getArtistId());
            pst.setString(3, artwork.getImageFileName());
            pst.setString(4, artwork.getTitle());
            pst.setString(5, artwork.getDescription());
            pst.setString(6, artwork.getExcerpt());
            pst.setInt(7, artwork.getArtWorkType());
            pst.setInt(8, artwork.getYearOfWork());
            pst.setInt(9, artwork.getWidth());
            pst.setInt(10, artwork.getHeight());
            pst.setString(11, artwork.getMedium());
            pst.setString(12, artwork.getOriginalHome());
            pst.setInt(13, artwork.getGalleryId());
            pst.setBigDecimal(14, artwork.getCost());
            pst.setBigDecimal(15, artwork.getMsrp());
            pst.setString(16, artwork.getArtWorkLink());
            pst.setString(17, artwork.getGoogleLink());
            pst.executeUpdate();
            flag = true;

        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            util.close(null, pst, conn);
        }
        return flag? artwork: null;
    }

    @Override
    public void delete(int artworkId) {
        Connection conn = util.getConnection();
        //language=MySQL
        String sql = "DELETE from artwork WHERE artworkId = ?";
        PreparedStatement pst = null;

        try{
            pst = conn.prepareStatement(sql);
            pst.setInt(1, artworkId);
            pst.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            util.close(null, pst, conn);
        }

    }

    @Override
    public Artwork getOne(int artworkId) {
        Connection conn = util.getConnection();
        String sql = "SELECT * FROM artwork WHERE artworkId = ?";
        PreparedStatement pst = null;
        ResultSet rs = null;
        Artwork artwork = null;
        try{
            pst = conn.prepareStatement(sql);
            pst.setInt(1, artworkId);
            rs = pst.executeQuery();
            if (rs.next()){
                artwork = buildArtwork(rs);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            util.close(rs, pst, conn);
        }

        return artwork;
    }

    @Override
    public Artwork update(Artwork artwork) {
        Connection conn = util.getConnection();
        String sql = "UPDATE `art`.`artwork` SET `ArtistID`=?, `ImageFileName`=?, `Title`=?," +
                " `Description`=?, `Excerpt`=?, `ArtWorkType`=?, `YearOfWork`=?, `Width`=?, `Height`=?," +
                " `Medium`=?, `OriginalHome`=?, `GalleryID`=?, `Cost`=?, `MSRP`=?, `ArtWorkLink`=?," +
                " `GoogleLink`=? WHERE `ArtWorkID`= ?";

        PreparedStatement pst = null;
        boolean flag = false;

        try{
            pst = conn.prepareStatement(sql);
            pst.setInt(1, artwork.getArtistId());
            pst.setString(2, artwork.getImageFileName());
            pst.setString(3, artwork.getTitle());
            pst.setString(4, artwork.getDescription());
            pst.setString(5, artwork.getExcerpt());
            pst.setInt(6, artwork.getArtWorkType());
            pst.setInt(7, artwork.getYearOfWork());
            pst.setInt(8, artwork.getWidth());
            pst.setInt(9, artwork.getHeight());
            pst.setString(10, artwork.getMedium());
            pst.setString(11, artwork.getOriginalHome());
            pst.setInt(12, artwork.getGalleryId());
            pst.setBigDecimal(13, artwork.getCost());
            pst.setBigDecimal(14, artwork.getMsrp());
            pst.setString(15, artwork.getArtWorkLink());
            pst.setString(16, artwork.getGoogleLink());
            pst.setInt(17, artwork.getArtWorkId());
            pst.executeUpdate();
            flag = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(null, pst, conn);
        }

        return flag? artwork: null;
    }

    @Override
    public int getCount() {
        Connection conn = util.getConnection();
        //language=MySQL
        String sql = "SELECT count(*) as C FROM `art`.`artwork`";
        PreparedStatement pst = null;
        ResultSet rs = null;
        int C = 0;

        try{
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            if (rs.next()){
                C = rs.getInt("C");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs, pst, conn);
        }
        return C;
    }

    @Override
    public List<Artwork> getByGallery(int galleryId) {
        Connection conn = util.getConnection();
        String sql = "SELECT * FROM artwork WHERE GalleryID = ?";
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Artwork> artworks = new ArrayList<>();

        try{
            pst = conn.prepareStatement(sql);
            pst.setInt(1, galleryId);
            rs = pst.executeQuery();
            while (rs.next()){
                artworks.add(buildArtwork(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs, pst, conn);
        }
        return artworks;
    }

    @Override
    public List<Artwork> getAll() {
        Connection conn = util.getConnection();
        String sql = "SELECT * FROM artwork";
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Artwork> artworks = new ArrayList<>();
        try{
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()){
                artworks.add(buildArtwork(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs, pst, conn);
        }

        return artworks;
    }

    @Override
    public List<Artwork> getByArtist(int artistID) {
        Connection conn = util.getConnection();
        String sql = "SELECT * FROM artwork WHERE `ArtistID` = ?";
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Artwork> artworks = new ArrayList<>();

        try{
            pst = conn.prepareStatement(sql);
            pst.setInt(1, artistID);
            rs = pst.executeQuery();
            while (rs.next()){
                artworks.add(buildArtwork(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs, pst, conn);
        }
        return artworks;
    }

    @Override
    public List<Artwork> getOnePage(int pageIndex, int pageSize) {
        Connection conn = util.getConnection();
        String sql = "SELECT * FROM artwork limit ?, ?";
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Artwork> artworks = new ArrayList<>();
        try{
            pst = conn.prepareStatement(sql);
            pst.setInt(1, (pageIndex) * pageSize);
            pst.setInt(2, pageSize);
            rs = pst.executeQuery();
            while (rs.next()){
                artworks.add(buildArtwork(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs, pst, conn);
        }

        return artworks;
    }

    @Override
    public List<Artwork> getByDecriptionContainingOrTitleContaining(String key1, String key2) {
        Connection conn = util.getConnection();
        String sql = "SELECT * FROM artwork WHERE Description LIKE  '%"+key1+"%' OR Title LIKE '%"+key2+"%'";
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<Artwork> artworks = new ArrayList<>();
        try{
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();
            while (rs.next()){
                artworks.add(buildArtwork(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(rs, pst, conn);
        }

        return artworks;
    }

    private Artwork buildArtwork(ResultSet rs) throws SQLException {
        Artwork artwork = new Artwork();

        artwork.setArtistId(rs.getInt("ArtistID"));
        artwork.setArtWorkId(rs.getInt("ArtWOrkID"));
        artwork.setArtWorkLink(rs.getString("ArtWorkLink"));
        artwork.setArtWorkType(rs.getInt("ArtWorkType"));
        artwork.setCost(rs.getBigDecimal("Cost"));
        artwork.setDescription(rs.getString("Description"));
        artwork.setExcerpt(rs.getString("Excerpt"));
        artwork.setGalleryId(rs.getInt("GalleryID"));
        artwork.setGoogleLink(rs.getString("GoogleLink"));
        artwork.setHeight(rs.getInt("Height"));
        artwork.setImageFileName(rs.getString("ImageFileName"));
        artwork.setMedium(rs.getString("Medium"));
        artwork.setMsrp(rs.getBigDecimal("MSRP"));
        artwork.setOriginalHome(rs.getString("OriginalHome"));
        artwork.setTitle(rs.getString("Title"));
        artwork.setWidth(rs.getInt("Width"));
        artwork.setYearOfWork(rs.getInt("YearOfWork"));

        return artwork;
    }
}
